#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

ADS_PRODUCT_COUNT="
INSERT OVERWRITE TABLE product_warehouse.ads_product_count
SELECT
    product_id,
    sum(uv) AS uv,
    sum(v_cnt) AS v_cnt,
    sum(pv) AS pv,
    sum(avg_stay_time) AS avg_stay_time,
    sum(if(session_pv = 1, 1, 0)) AS new_user_count,
    sum(cart_count) AS cart_count
FROM (
         SELECT
             product_id,
             COUNT(DISTINCT user_id) AS uv,
             count(DISTINCT if(event_type='click',user_id,NULL)) AS v_cnt,
             count(*) pv,
             avg(stay_time) AS avg_stay_time,
             count(session_id) AS session_pv,
             sum(if(event_type='cart',1,0)) AS cart_count
         FROM product_warehouse.dwd_product_inc
         WHERE dt='${data_date}'
         GROUP BY product_id
     )t1
GROUP BY product_id;
"

ADS_SKU_COUNT="
INSERT OVERWRITE TABLE product_warehouse.ads_sku_count
SELECT
    sku_id,
    count(if(event_type='cart',sku_id,NULL)) AS cart_cnt,
    sum(sku_price) AS price,
    sum(if(status='已支付',1,0)) AS zf_cnt,
    count(if(status='已支付',user_id,NULL)) AS user_cnt
FROM product_warehouse.dwd_product_inc
GROUP BY sku_id;
"

ADS_PAY_COUNT="
INSERT OVERWRITE TABLE product_warehouse.ads_pay_count
SELECT
    sku_id,
    price,
    round(price/sum(price) OVER() * 100,4) AS zf_rate,
    zf_cnt,
    user_cnt,
    round(user_cnt*1.0/sum(user_cnt) OVER() * 100,4) AS zf_user_rate
FROM (
         SELECT
             sku_id,
             sum(sku_price) AS price,
             sum(if(status='已支付',1,0)) AS zf_cnt,
             count(if(event_type='cart',sku_id,NULL)) AS cart_num,
             count(if(status='已支付',user_id,NULL)) AS user_cnt
         FROM product_warehouse.dwd_product_inc
         GROUP BY sku_id
     );
"

ADS_METHOD="
INSERT OVERWRITE TABLE product_warehouse.ads_method
SELECT
    count(DISTINCT user_id) AS user_cnt,
    count(DISTINCT if(event_type='favorite',user_id,NULL)) AS favorite_cnt,
    count(DISTINCT if(event_type='cart',user_id,NULL)) AS cart_rate,
    count(if(status='已支付',user_id,NULL)) AS user_cnt
FROM product_warehouse.dwd_product_inc;
"

ADS_PRICE_ANALYSIS="
INSERT OVERWRITE TABLE product_warehouse.ads_price_analysis
SELECT
    product_id,product_name,
    category_name,
    rating,
    sum(product_price) AS price,
    count(DISTINCT user_id) AS user_cnt,
    count(if(status='已支付',user_id,NULL)) AS count,
    count(if(status='已支付',user_id,NULL))/count(DISTINCT user_id) AS pay_rate
FROM product_warehouse.dwd_product_inc
WHERE rating=5
GROUP BY product_id,product_name,category_name,rating
ORDER BY pay_rate DESC LIMIT 5;
"

ADS_PRODUCT_INFORMATION="
INSERT OVERWRITE TABLE product_warehouse.ads_product_information
SELECT
    product_id,
    count(DISTINCT user_id) AS uv,
    count(if(status='已支付',user_id,NULL)) AS v_cnt,
    count(if(status='已支付',user_id,NULL))/count(DISTINCT user_id) AS pay_rate,
    count(if(event_type='favorite',user_id,NULL)) AS favorite_cnt,
    count(if(event_type='cart',user_id,NULL)) AS cart_count
FROM product_warehouse.dwd_product_inc
GROUP BY product_id;
"

ADS_USER_PROPORTION="
INSERT OVERWRITE TABLE product_warehouse.ads_user_proportion
SELECT
    age_group,
    user_cnt,
    round(user_cnt/sum(user_cnt) over()*100,2) AS user_percent
FROM (
         SELECT
             age_group,
             count(user_id) AS user_cnt
         FROM (
                  SELECT
                      user_id,
                      case
                          when age >= 18 and age <= 24 then '18-24岁'
                          when age >= 25 and age <= 29 then '25-29岁'
                          when age >= 30 and age <= 34 then '30-39岁'
                          when age >= 35 and age <= 49 then '40-49岁'
                          when age >= 50 then '50岁及以上'
                          else '未知'
                          end as age_group
                  FROM product_warehouse.dwd_product_inc
                  GROUP BY user_id,age_group
              )t
         GROUP BY age_group
);
"

ADS_USER_GENDER="
INSERT OVERWRITE TABLE product_warehouse.ads_user_gender
SELECT
    gender,
    COUNT(*) AS user_count,
    round(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(),2) AS percentage
FROM product_warehouse.dwd_product_inc
WHERE gender IS NOT NULL
GROUP BY gender;
"

ADS_PRICE_EVALUATION="
INSERT OVERWRITE TABLE product_warehouse.ads_price_evaluation
SELECT
    count(if(rating=0,user_id,NULL)) AS vivid_cnt,
    count(if(rating=2,user_id,NULL)) AS negative_cnt,
    count(if(rating=1,user_id,NULL)) AS positive_cnt,
    count(if(rating=4,user_id,NULL)) AS new_user_cnt,
    count(if(rating=3,user_id,NULL)) AS old_user_cnt,
    count(if(rating=5,user_id,NULL)) AS old_user_cnt,
    count(*)
FROM product_warehouse.dwd_product_inc;
"

ADS_VIEW_CART="
INSERT OVERWRITE TABLE product_warehouse.ads_view_cart
SELECT
    count(if(event_type='click',user_id,NULL)) AS vivid_cnt,
    count(if(start_id=1,user_id,NULL)) AS view_cnt,
    count(if(is_favorite=1,user_id,NULL)) AS is_favorite,
    count(if(is_cart=1,user_id,NULL)) AS is_cart,
    round(sum(if(seed=1,product_price,0)),2) AS price,
    count(if(seed=1 and status='完成',user_id,NULL)) AS user_cnt
FROM product_warehouse.dwd_product_inc;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${ADS_PRODUCT_COUNT}${ADS_SKU_COUNT}${ADS_PAY_COUNT}${ADS_METHOD}${ADS_PRICE_ANALYSIS}${ADS_PRODUCT_INFORMATION}${ADS_USER_PROPORTION}${ADS_USER_GENDER}${ADS_PRICE_EVALUATION}${ADS_VIEW_CART}"